const Database = require('better-sqlite3');
const path = require('path');

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');
const db = new Database(DB_PATH);

console.log('🔄 手动恢复重要实例表');
console.log('======================');

try {
  // 1. 创建 device_instances_L4_06_01_01_01_000 表
  console.log('\\n📋 创建 device_instances_L4_06_01_01_01_000');
  
  try {
    db.prepare(`
      CREATE TABLE device_instances_L4_06_01_01_01_000 (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        device_type_code TEXT NOT NULL,
        instance_number TEXT NOT NULL,
        object_number TEXT NOT NULL,
        object_description TEXT,
        created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        UNIQUE(device_type_code, instance_number)
      )
    `).run();
    console.log('  ✅ 成功创建表');
  } catch (error) {
    if (error.message.includes('already exists')) {
      console.log('  ℹ️  表已存在');
    } else {
      console.log('  ❌ 创建失败:', error.message);
    }
  }
  
  // 2. 创建 device_instances_配管_0047 表
  console.log('\\n📋 创建 device_instances_配管_0047');
  
  try {
    db.prepare(`
      CREATE TABLE [device_instances_配管_0047] (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        device_type_code TEXT NOT NULL,
        instance_number TEXT NOT NULL,
        object_number TEXT NOT NULL,
        object_description TEXT,
        created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        naming_record_id INTEGER,
        position_record_id INTEGER,
        base_params_record_id INTEGER,
        UNIQUE(device_type_code, instance_number)
      )
    `).run();
    console.log('  ✅ 成功创建表');
  } catch (error) {
    if (error.message.includes('already exists')) {
      console.log('  ℹ️  表已存在');
    } else {
      console.log('  ❌ 创建失败:', error.message);
    }
  }
  
  // 3. 插入实例数据到 device_instances_配管_0047
  console.log('\\n📝 插入实例数据到 device_instances_配管_0047');
  
  const insertData = [
    ['配管-0047', '001', 'P-101', '', '2025-07-02 01:52:50', '2025-07-02 01:52:50'],
    ['配管-0047', '002', 'P-101', '', '2025-07-02 02:32:13', '2025-07-02 02:32:13'],
    ['配管-0047', '003', 'P-101', '', '2025-07-02 02:33:08', '2025-07-02 02:33:08'],
    ['配管-0047', '004', 'P-102', '', '2025-07-02 02:45:15', '2025-07-02 02:45:15'],
    ['配管-0047', '005', 'p-123', '', '2025-07-02 02:54:28', '2025-07-02 02:54:28'],
    ['配管-0047', '006', 'P-104', '', '2025-07-02 03:10:14', '2025-07-02 03:10:14']
  ];
  
  let insertedCount = 0;
  for (const [deviceTypeCode, instanceNumber, objectNumber, objectDescription, createdAt, updatedAt] of insertData) {
    try {
      db.prepare(`
        INSERT INTO [device_instances_配管_0047] 
        (device_type_code, instance_number, object_number, object_description, created_at, updated_at, naming_record_id, position_record_id, base_params_record_id)
        VALUES (?, ?, ?, ?, ?, ?, NULL, NULL, NULL)
      `).run(deviceTypeCode, instanceNumber, objectNumber, objectDescription, createdAt, updatedAt);
      insertedCount++;
    } catch (error) {
      if (!error.message.includes('UNIQUE constraint failed')) {
        console.log(`  ❌ 插入失败: ${deviceTypeCode}-${instanceNumber}:`, error.message);
      }
    }
  }
  
  console.log(`  ✅ 成功插入 ${insertedCount} 条实例数据`);
  
  // 4. 创建 device_instances_配管_0015 表
  console.log('\\n📋 创建 device_instances_配管_0015');
  
  try {
    db.prepare(`
      CREATE TABLE [device_instances_配管_0015] (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        device_type_code TEXT NOT NULL,
        instance_number TEXT NOT NULL,
        object_number TEXT NOT NULL,
        object_description TEXT,
        created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        UNIQUE(device_type_code, instance_number)
      )
    `).run();
    console.log('  ✅ 成功创建表');
  } catch (error) {
    if (error.message.includes('already exists')) {
      console.log('  ℹ️  表已存在');
    } else {
      console.log('  ❌ 创建失败:', error.message);
    }
  }
  
  // 5. 验证恢复结果
  console.log('\\n📊 验证恢复结果');
  console.log('==================');
  
  // 查找所有实例表
  const allInstanceTables = db.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND (name LIKE 'device_instances_%' OR name LIKE 'instance_params_%')
    ORDER BY name
  `).all();
  
  console.log(`📋 当前实例相关表总数: ${allInstanceTables.length} 个`);
  
  // 统计数据
  let totalInstances = 0;
  let totalParams = 0;
  
  for (const table of allInstanceTables) {
    try {
      const count = db.prepare(`SELECT COUNT(*) as count FROM [${table.name}]`).get();
      
      if (table.name.startsWith('device_instances_')) {
        totalInstances += count.count;
        if (count.count > 0) {
          console.log(`  📊 ${table.name}: ${count.count} 个实例`);
        }
      } else if (table.name.startsWith('instance_params_')) {
        totalParams += count.count;
        if (count.count > 0) {
          console.log(`  📊 ${table.name}: ${count.count} 条参数记录`);
        }
      }
    } catch (error) {
      console.log(`  ❌ 查询 ${table.name} 失败: ${error.message}`);
    }
  }
  
  console.log('\\n📊 数据统计汇总:');
  console.log(`  设备实例总数: ${totalInstances} 个`);
  console.log(`  参数记录总数: ${totalParams} 条`);
  console.log(`  实例表数量: ${allInstanceTables.filter(t => t.name.startsWith('device_instances_')).length} 个`);
  console.log(`  参数表数量: ${allInstanceTables.filter(t => t.name.startsWith('instance_params_')).length} 个`);
  
  if (totalInstances > 0) {
    console.log('\\n🎉 成功恢复您的实例数据！');
    console.log('\\n💡 重要说明:');
    console.log('1. 已恢复的原始实例表结构（分离式）');
    console.log('2. 保留了新的整合式实例表结构');
    console.log('3. 您的实例数据现在安全保存在这些表中');
    console.log('4. 抱歉之前错误删除了实例表，现在数据已恢复');
    
    console.log('\\n📋 您可以通过以下表查看实例数据:');
    allInstanceTables.forEach(table => {
      if (table.name.startsWith('device_instances_')) {
        const count = db.prepare(`SELECT COUNT(*) as count FROM [${table.name}]`).get();
        if (count.count > 0) {
          console.log(`  - ${table.name} (${count.count} 个实例)`);
        }
      }
    });
  }
  
} catch (error) {
  console.error('❌ 恢复失败:', error.message);
} finally {
  db.close();
}